package dao;

import bean.Mail;
import util.C3p0Util;
import util.DruidUtil;
import util.StringUtil;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;


public class MailDao {

    Connection conn=null;
    public boolean setReply(int replyId,String replyContent){
        String sql="update egov_mailbox set reply = ? ,replydate=CURRENT_TIMESTAMP where mailId = ?";
        try {
            conn= DruidUtil.getConnection();
            PreparedStatement ps=conn.prepareStatement(sql);
            ps.setString(1,replyContent);
            ps.setInt(2,replyId);
            boolean result=ps.execute();
            conn.close();
            return result;
        } catch (SQLException e) {
            e.printStackTrace();
            return false;
        }
    }
    public List<Mail> getMailList(String title, String search_type, int pageNum){
        ArrayList<Mail> Mail =new ArrayList<Mail>();
        int k=(pageNum-1)*5;
        StringBuffer sql=new StringBuffer();
        int search_type_int=0;
        String typeStr[]={"建议","投诉","举报","指南","其他"};
        for(int i=0;i<typeStr.length;++i){
            if (typeStr[i].equals(search_type)) search_type_int = i;
        }
        sql.append("select * from egov_mailbox where   1=1");
        if(title.length()!=0){
            sql.append("  and  mailTitle like ? ");
        }
        if(search_type.length()!=0){
            sql.append(" and  mailType = ? ");
        }
        sql.append(" and ispublic = 1 ");
        sql.append(" limit "+k+",5");
        try {
            conn= DruidUtil.getConnection();
            PreparedStatement ps=conn.prepareStatement(sql.toString());
            int index=1;
            if(title.length()!=0){
                ps.setString(index,"%"+title+"%");
                index++;
            }
            if(search_type.length()!=0){
                ps.setInt(index,search_type_int);
                index++;
            }
            ResultSet rs=ps.executeQuery();
            while(rs.next()){
                Mail m=new Mail();
                m.setMailId(rs.getInt("mailId"));
                m.setMailType(rs.getInt("mailType"));
                m.setMailTitle(rs.getString("mailTitle"));
                m.setName(rs.getString("name"));
                m.setPhone(rs.getString("phone"));
                m.setAddress(rs.getString("address"));
                m.setEmail(rs.getString("email"));
                //m.setMailFile();
                m.setIspublic(rs.getInt("ispublic"));
                //m.setPrivacy();
                m.setContent(rs.getString("content"));
                m.setReply(rs.getString("reply"));
                m.setPostdate(rs.getTimestamp("postdate").toString());
                if(rs.getTimestamp("replydate")!=null) m.setReplydate(rs.getTimestamp("replydate").toString());
                m.setSearchKey(rs.getString("searchKey"));
                Mail.add(m);
            }
            conn.close();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return Mail;
    }
    public int getMailListCount(String title,String search_type){
        int count=0;
        StringBuffer sql=new StringBuffer();
        int search_type_int=0;
        String typeStr[]={"建议","投诉","举报","指南","其他"};
        for(int i=0;i<typeStr.length;++i){
            if (typeStr[i].equals(search_type)) search_type_int = i;
        }
        sql.append("select count(*) from egov_mailbox  where 1=1");
        if(title.length()!=0){
            sql.append(" and  mailTitle like ? ");
        }
        if(search_type.length()!=0){
            sql.append(" and   mailType = ? ");
        }
        sql.append(" and ispublic = 1 ");
        try {
            conn= DruidUtil.getConnection();
            PreparedStatement ps=conn.prepareStatement(sql.toString());
            int index=1;
            if(title.length()!=0) {
                ps.setString(index,"%"+title+"%");
                index++;
            }
            if(search_type.length()!=0){
                ps.setInt(index,search_type_int);
                index++;
            }
            ResultSet rs=ps.executeQuery();
            while(rs.next()){
                count=rs.getInt(1);
            }
            conn.close();
        } catch (SQLException e) {
            e.printStackTrace();
        }

        return  count;
    }
    public boolean mailAdd(Mail mail)throws Exception{
        StringBuffer sb1= new StringBuffer("insert into egov_mailbox(");
        StringBuffer sb2=new StringBuffer(" values( ");
        List<Object> param = new ArrayList<Object>();

        sb1.append(",mailType ");
        sb2.append(" ,?");
        param.add(Integer.valueOf(mail.getMailType()));


        if(StringUtil.isNotEmpty(mail.getMailTitle())){
            sb1.append(",mailTitle ");
            sb2.append(" ,?");
            param.add(mail.getMailTitle());
        }


        if(StringUtil.isNotEmpty(mail.getName())){
            sb1.append(",name ");
            sb2.append(" ,?");
            param.add(mail.getName());
        }


        if(StringUtil.isNotEmpty(mail.getPhone())){
            sb1.append(",phone ");
            sb2.append(" ,?");
            param.add(mail.getPhone());
        }


        if(StringUtil.isNotEmpty(mail.getAddress())){
            sb1.append(",address ");
            sb2.append(" ,?");
            param.add(mail.getAddress());
        }

        if(StringUtil.isNotEmpty(mail.getEmail())){
            sb1.append(" ,email ");
            sb2.append(" ,?");
            param.add(mail.getEmail());
        }
        if(StringUtil.isNotEmpty(mail.getMailFile())){
            sb1.append(", mailFile");
            sb2.append(" ,?");
            param.add(mail.getMailFile());
        }

        sb1.append(", ispublic");
        sb2.append(" ,?");
        param.add(Integer.valueOf(mail.getIspublic()));

        if(StringUtil.isNotEmpty(mail.getContent())){
            sb1.append(" , content ");
            sb2.append(" ,?");
            param.add(mail.getContent());
        }
        sb1.append(",searchKey");
        sb2.append(", ? ");
        param.add(mail.getSearchKey());
        sb1.append(",postdate ");
        sb2.append(", current_timestamp()");
        sb1.append(")");
        sb2.append(")");
        String sql1 = sb1.toString().replaceFirst(",", " ");
        String sql2 = sb2.toString().replaceFirst(",", " ");
        System.out.println(sql1+sql2);
        int seq = 1;
        conn= DruidUtil.getConnection();
        PreparedStatement ps=conn.prepareStatement(sql1+sql2);
        for (Object elem : param) {
            if (elem instanceof String) {
                ps.setString(seq, (String) elem);
            } else if (elem instanceof Integer) {
                ps.setInt(seq, (Integer) elem);
            }
            seq++;
        }
       if (ps.execute()){
           conn.close();
           return true;
       }
        conn.close();
       return false;
    }

    public Mail getMailById(int id){
        Mail m=new Mail();
        String sql="select * from egov_mailbox where  mailId = ? ";
        try {
            conn= DruidUtil.getConnection();
            PreparedStatement ps=conn.prepareStatement(sql);
            ps.setInt(1,id);
            ResultSet rs=ps.executeQuery();
            while(rs.next()){
                m.setMailId(rs.getInt("mailId"));
                m.setMailType(rs.getInt("mailType"));
                m.setName(rs.getString("name"));
                m.setMailTitle(rs.getString("mailTitle"));
                m.setPhone(rs.getString("phone"));
                m.setAddress(rs.getString("address"));
                m.setEmail(rs.getString("email"));
                //m.setMailFile();
                m.setIspublic(rs.getInt("ispublic"));
                //m.setPrivacy();
                m.setContent(rs.getString("content"));
                m.setReply(rs.getString("reply"));
                m.setPostdate(rs.getTimestamp("postdate").toString());
                if(rs.getTimestamp("replydate")!=null) m.setReplydate(rs.getTimestamp("replydate").toString());
                m.setSearchKey(rs.getString("searchKey"));
            }
            conn.close();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return  m;
    }
    public Mail getMailByKey(String searchKey){
        Mail m=new Mail();
        String sql="select * from egov_mailbox where  SearchKey = ? ";
        try {
            conn= DruidUtil.getConnection();
            PreparedStatement ps=conn.prepareStatement(sql);
            ps.setString(1,searchKey);
            ResultSet rs=ps.executeQuery();
            while(rs.next()){
                m.setMailId(rs.getInt("mailId"));
                m.setMailType(rs.getInt("mailType"));
                m.setName(rs.getString("name"));
                m.setMailTitle(rs.getString("mailTitle"));
                m.setPhone(rs.getString("phone"));
                m.setAddress(rs.getString("address"));
                m.setEmail(rs.getString("email"));
                //m.setMailFile();
                m.setIspublic(rs.getInt("ispublic"));
                //m.setPrivacy();
                m.setContent(rs.getString("content"));
                m.setReply(rs.getString("reply"));
                m.setPostdate(rs.getTimestamp("postdate").toString());
                if(rs.getTimestamp("replydate")!=null) m.setReplydate(rs.getTimestamp("replydate").toString());
                m.setSearchKey(rs.getString("searchKey"));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        if(m.getPostdate()!=null)
            return  m;
        else return null;
    }

    public void deleteMailById(int id) {
        Mail m=new Mail();
        String sql="DELETE  from egov_mailbox where  mailId = ? ";
        try {
            conn= DruidUtil.getConnection();
            PreparedStatement ps=conn.prepareStatement(sql);
            ps.setInt(1,id);
            ps.execute();
            conn.close();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }


    public List<Mail> getMailListBackend(String title, String search_type, int pageNum){
        ArrayList<Mail> Mail =new ArrayList<Mail>();
        int k=(pageNum-1)*5;
        StringBuffer sql=new StringBuffer();
        int search_type_int=0;
        String typeStr[]={"建议","投诉","举报","指南","其他"};
        for(int i=0;i<typeStr.length;++i){
            if (typeStr[i].equals(search_type)) search_type_int = i;
        }
        sql.append("select * from egov_mailbox where   1=1 ");
        if(title.length()!=0){
            sql.append("  and  mailTitle like ? ");
        }
        if(search_type.length()!=0){
            sql.append(" and  mailType = ? ");
        }

        sql.append(" limit "+k+",5");
        System.out.println(sql.toString());
        try {
            conn= DruidUtil.getConnection();
            PreparedStatement ps=conn.prepareStatement(sql.toString());
            int index=1;
            if(title.length()!=0){
                ps.setString(index,"%"+title+"%");
                index++;
            }
            if(search_type.length()!=0){
                ps.setInt(index,search_type_int);
                index++;
            }
            ResultSet rs=ps.executeQuery();
            while(rs.next()){
                Mail m=new Mail();
                m.setMailId(rs.getInt("mailId"));
                m.setMailType(rs.getInt("mailType"));
                m.setMailTitle(rs.getString("mailTitle"));
                m.setName(rs.getString("name"));
                m.setPhone(rs.getString("phone"));
                m.setAddress(rs.getString("address"));
                m.setEmail(rs.getString("email"));
                //m.setMailFile();
                m.setIspublic(rs.getInt("ispublic"));
                //m.setPrivacy();
                m.setContent(rs.getString("content"));
                m.setReply(rs.getString("reply"));
                m.setPostdate(rs.getTimestamp("postdate").toString());
                if(rs.getTimestamp("replydate")!=null) m.setReplydate(rs.getTimestamp("replydate").toString());
                m.setSearchKey(rs.getString("searchKey"));
                Mail.add(m);
            }
            conn.close();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return Mail;
    }

    public int getMailListCountBackend(String title,String search_type){
        int count=0;
        StringBuffer sql=new StringBuffer();
        int search_type_int=0;
        String typeStr[]={"建议","投诉","举报","指南","其他"};
        for(int i=0;i<typeStr.length;++i){
            if (typeStr[i].equals(search_type)) search_type_int = i;
        }
        sql.append("select count(*) from egov_mailbox  where 1=1");
        if(title.length()!=0){
            sql.append(" and  mailTitle like ? ");
        }
        if(search_type.length()!=0){
            sql.append(" and   mailType = ? ");
        }

        try {
            conn= DruidUtil.getConnection();
            PreparedStatement ps=conn.prepareStatement(sql.toString());
            int index=1;
            if(title.length()!=0) {
                ps.setString(index,"%"+title+"%");
                index++;
            }
            if(search_type.length()!=0){
                ps.setInt(index,search_type_int);
                index++;
            }
            ResultSet rs=ps.executeQuery();
            while(rs.next()){
                count=rs.getInt(1);
            }
            conn.close();
        } catch (SQLException e) {
            e.printStackTrace();
        }

        return  count;
    }
}
